package team.sun.sss.dao.student;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;
import team.sun.sss.pojo.hardwork.School;
import team.sun.sss.pojo.student.Student1;

import java.util.List;
import java.util.Map;

@Mapper
@Repository
public interface StudentDao {
    //模糊查询
    @Select("select * from student where stu_name LIKE CONCAT('%',#{stuName},'%')")
    List<Student1>selectStuByName(String stuName);
    @Select("select * from student where stu_id LIKE CONCAT('%',#{stuId},'%')")
    List<Student1>selectStuByStuId(int stuId);

    @Select("select * from student where stu_grade=#{stuGrade} and stu_college=#{stuCollege}  and stu_major=#{stuMajor} " +
            "and stu_class=#{stuClass}")
    List<Student1>selectStuByAll(String stuGrade,String stuCollege,String stuMajor, String stuClass);

    //根据学生ID获取学生的信息
    @Select("select * from student where stu_id=#{stuId}")
    List<Student1>selectStuById(int stuId);
    //2.
    @Select("select * from student where stu_id=#{stuId}")
    Student1 StuById(int stuId);
    //学生修改自己的信息
    //可修改家庭住址、邮政编码、政治面貌、qq、微信、手机号码、邮箱、特长、既往病史，, stu_bank_id=#{stuBankId，Stringa stuBankId
   @Update("update student set stu_address=#{stuAddress}, stu_postal_code=#{stuPostalCode} ," +
            "stu_qq=#{stuQq} ,stu_wechat=#{stuWechat}, stu_phone=#{stuPhone}, stu_email=#{stuEmail}, stu_advantage=#{stuAdvantage}," +
            " stu_past_ill=#{stuPastIll} ,stu_marriage=#{stuMarriage},stu_bank_Id=#{stuBankId}, stu_bank_name=#{stuBankName} " +
           ",stu_bank_address=#{stuBankAddress} where stu_id=#{stuId}")
    int studentUpdate(int stuId,String stuAddress, String stuPostalCode, String stuQq,
                      String stuWechat, String stuPhone,String stuEmail,String stuAdvantage,String stuPastIll,String stuMarriage,String stuBankId,
                      String stuBankName,String stuBankAddress);


    //辅导员获取所带班的学生信息，可能出现同名的辅导员，同时需要学生同年级、同学院、同专业、同班级
    @Select("select * from student where stu_instructor=#{stuInstructor}")
    List<Student1>selectStuByInstructor(String stuInstructor);

    @Select("select * from student where stu_instructor=#{stuInstructor}")
    Student1 selectStuByInstructor2(String stuInstructor);
    //辅导员修改部分学生的信息:
    @Update("update student set stu_special_attention=#{stuSpecialAttention} where stu_id=#{stuId}")
    int updateStudentAttention(int stuId,String stuSpecialAttention);
//辅导显示对应的班级

    @Select("select DISTINCT stu_major from student where stu_instructor=#{stuInstructor}")
    List<Student1>selectStuByMajor(String stuInstructor);
    @Select("select * from student where stu_instructor=#{stuInstructor} and stu_major=#{stuMajor}")
    List<Student1>selectStuMajor(String stuInstructor,String stuMajor);
    //学工获取所有学生的信息,之后可能需要按年级、学院、专业、班级进行分类
    @Select("select * from student")
    List<Student1>studentList();
    @Select("select stu_id from student where stu_id Not in (${stuId})")
    List<Student1>stuIdList(int stuId);
    //Student1 stuIdList();
    //学工添加学生信息
    @Insert("insert into student (stu_img, stu_Id,stu_name, stu_past_name , stu_sex,stu_birth, stu_id_card, stu_province, " +
         "stu_country, stu_nation,stu_marriage, stu_dormitory,stu_address,stu_postal_code,stu_political_outlook," +
         " stu_qq, stu_wechat, stu_phone, stu_email,stu_advantage, stu_past_ill, "+
         "stu_bank_id, stu_college,stu_major, stu_grade,stu_class, stu_states, stu_length,stu_enter_time,stu_level,"+
         "stu_source, stu_exam_num, stu_instructor, stu_is_poor,stu_assistance_level) values (#{stuImg}, #{stuId},#{stuName}, #{stuPastName}, #{stuSex} " +
         ",#{stuBirth }, #{stuIdCard } ,#{stuProvince}, #{stuCountry } , #{stuNation } ,#{stuMarriage }, #{stuDormitory },#{stuAddress}" +
         " ,#{stuPostalCode} ,#{stuPoliticalOutlook},#{stuQq}, #{stuWechat}, #{stuPhone} , #{stuEmail},#{stuAdvantage} ,#{stuPastIll} ," +
         "#{stuBankId},#{stuCollege} ,#{stuMajor} ,#{stuGrade},#{stuClass} ,#{stuStates} ,#{stuLength} ,#{stuEnterTime} ,#{stuLevel} ," +
         "#{stuSource} ,#{stuExamNum} ,#{stuInstructor} ,#{stuIsPoor} ,#{stuAssistanceLevel}) ")
 int insertStudent(Student1 student1);
    //学工修改所有学生信息

    @Update("update student set stu_img=#{stuImg},stu_id=#{stuId},stu_name=#{stuName},stu_past_name=#{stuPastName}, stu_sex=#{stuSex}," +
            "stu_birth=#{stuBirth}, stu_id_card=#{stuIdCard}, stu_province=#{stuProvince}, " +
            "  stu_country=#{stuCountry}, stu_nation=#{stuNation},stu_marriage=#{stuMarriage}, stu_dormitory=#{stuDormitory } ," +
            "stu_address=#{stuAddress} ,stu_postal_code=#{stuPostalCode} ,stu_political_outlook=#{stuPoliticalOutlook} ," +
            " stu_qq=#{stuQq}, stu_wechat=#{stuWechat}, stu_phone=#{stuPhone}, stu_email=#{stuEmail}, " +
            "stu_advantage=#{stuAdvantage}, stu_past_ill=#{stuPastIll}, stu_bank_id=#{stuBankId}, stu_college=#{stuCollege}," +
            "stu_major=#{stuMajor}, stu_grade=#{stuGrade},stu_class=#{stuClass}, stu_states=#{stuStates}, stu_length=#{stuLength}," +
            "stu_enter_time=#{stuEnterTime},stu_level=#{stuLevel},stu_source=#{stuSource}, stu_exam_num=#{stuExamNum}," +
            " stu_instructor=#{stuInstructor}, stu_is_poor=#{stuIsPoor},stu_type=#{stuType},stu_enter_type=#{stuEnterType} ," +
            "stu_pinyin=#{stuPinyin} ,stu_bank_name=#{stuBankName},stu_bank_address=#{stuBankAddress},stu_assistance_level=#{stuAssistanceLevel}," +
            "stu_special_attention=#{stuSpecialAttention} where stu_id=#{stuIdPast}")
    int staffUpdateStu(String stuImg,int  stuId,String  stuName,String  stuPastName, String  stuSex,
                            String stuBirth, String stuIdCard,String stuProvince,String stuCountry, String  stuNation,
                       String stuMarriage, String stuDormitory ,
                            String stuAddress ,String stuPostalCode ,String stuPoliticalOutlook,
                            String stuQq,String stuWechat, String stuPhone, String stuEmail, String stuAdvantage,
                            String stuPastIll,String stuBankId, String stuCollege,String stuMajor, String stuGrade,String stuClass,
                            String stuStates, String stuLength,String stuEnterTime,String stuLevel,String stuSource, String stuExamNum,
                            String stuInstructor, String stuIsPoor,String stuType,String stuEnterType,
                       String stuPinyin,String stuBankName,String stuBankAddress,String stuAssistanceLevel, String stuSpecialAttention, int stuIdPast);
    //查询学校专业
    @Select("select  * from school ")
    List<School>schoolList();
    //填写勤工俭学申请表，对一些数据进行修改更新
    @Update("update student set stu_bank_id=#{bankId},stu_bank_name=#{stuBankName},stu_bank_address=#{stuBankAddress} where stu_id=#{stuId}")
    int applyUpdateStu(int stuId,String bankId,String stuBankName,String stuBankAddress);

    @Select("select * from student where stu_id=#{stu_id}")
    List<Map<String,Object>> getUserList(int stu_id);
}
